
from java.sql import Connection
from java.sql import PreparedStatement
from java.sql import ResultSet
from java.sql import Timestamp
from java.sql import SQLException

from java.util import Calendar
from java.util import SimpleTimeZone

from au.csiro.ict.tasman.core import ConfigurationParser
from au.csiro.ict.tasman.core import Message
from au.csiro.ict.tasman.configuration import Configuration
from au.csiro.ict.tasman.datatype import Annotation
from au.csiro.ict.tasman.datatype import Sample
from au.csiro.ict.tasman.util.database import JdbcDatabaseConnection

from core import Dispatcher

from com.xhaus.jyson import JysonCodec as json


def execute():
  cn = get_database_connection()
  
  try:
    if isinstance(message.data, Sample):
      sample = message.data
      sensor_uid = natural_to_unique_address(sample.getSensorId(), cn)
      
      if sensor_uid is None:
	processor.getLogger().error("Sensor UID not found in database, most likely sensor has not been added.")
      else:
	utc_calendar = Calendar.getInstance()
	utc_calendar.setTimeZone(SimpleTimeZone(0, "UTC"))
	timestamp = Timestamp(sample.time.toDate().getTime())
      
	dml = "insert into sample (uid, time, value, sensor_uid, annotation) values (nextval('sample_sequence'),?,?,?,?)"
	
	statement = cn.prepareStatement(dml)
	statement.setTimestamp(1, timestamp, utc_calendar)
	statement.setDouble(2, sample.value)
	statement.setInt(3, int(sensor_uid))
	statement.setString(4, sample.getTags().toString())
	rows_manipulated = execute_dml(statement)
	if rows_manipulated is None or rows_manipulated == 0:
	  processor.getLogger().error("Error: sample insert failed to return a valid number of rows manipulated: " + str(rows_manipulated))


    if isinstance(message.data, Annotation):
      annotation = message.data
      sensor_uid = natural_to_unique_address(annotation.getSensorId(), cn)

      dml = "insert into statistics (uid, sensor_uid, annotation) values (nextval('statistics_sequence'),?,?)"
	
      statement = cn.prepareStatement(dml)
      statement.setInt(1, sensor_uid)
      statement.setString(2, annotation.getTags().toString())
      rows_manipulated = execute_dml(statement)
      if rows_manipulated is None or rows_manipulated == 0:
	processor.getLogger().error("Error: annotation insert failed to return a valid number of rows manipulated: " + str(rows_manipulated))  
  except Exception, e:
    processor.getLogger().error("exception caught whilst executing DML statement: " + str(e))
    
  try:
    cn.close()
    cn = None
  except Exception, e:
    processor.getLogger().error("Error: exception thrown whilst trying to close connection: " + str(e))
  return message
  
  
def get_database_connection():
  jdbc = JdbcDatabaseConnection()
  db_config = json.loads(processor.getConfiguration().getConfiguration().toString())
  return jdbc.getConnection(db_config['dbType'], db_config['dbServer'], db_config['dbPort'], db_config['dbName'], db_config['dbDriver'], db_config['dbUsername'], db_config['dbPassword'])



def natural_to_unique_address(sensor_id, cn):
  nfs = sensor_id.split(".")
  sensor_uid = None

  qry = "select sensor.uid from sensor inner join feature on feature.uid = sensor.feature_uid inner join network on network.uid = feature.network_uid where network.id = ? and feature.id = ? and sensor.id = ?"

  try:
    statement = cn.prepareStatement(qry, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
    statement.clearParameters()
    statement.setString(1, nfs[0])
    statement.setString(2, nfs[1])
    statement.setString(3, nfs[2])
    rs = execute_query(statement)
    while rs.next():
      sensor_uid = rs.getInt("uid")
  except Exception, e:
    processor.getLogger().error("exception caught whilst accessing resultset: " + str(e))
  return sensor_uid
  
  
def execute_query(statement):
  rs = None
  try:
    rs = statement.executeQuery()
  except SQLException, e:
    processor.getLogger().error("exception caught whilst executing query: " + str(e))
  except Exception, e:
    processor.getLogger().error("exception caught whilst executing query: " + str(e))
  return rs
  
  
def execute_dml(statement):
  rs = None
  try:
    rs = statement.executeUpdate()
  except SQLException, e:
    processor.getLogger().error("exception caught whilst executing update: " + str(e))
  except Exception, e:
    processor.getLogger().error("exception caught whilst executing update: " + str(e))
  return rs
  